package zozopo.warehouse.operations;

import java.io.IOException;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import zozopo.comm.CNSpell;
import zozopo.database.BusiDatabaseManager;
import zozopo.database.SqlMachine;


import zozopo.manual.operations.ReleaseTB;
import zozopo.operations.comm.OperatingService;
import zozopo.user.operations.UserTB;


public class GoodsOS extends OperatingService{
	private static final Log logger = LogFactory.getLog(GoodsOS.class);
	
	public GoodsOS(Connection conn){
		super(conn);
	}
	
	public GoodsOS(){
		this.databaseConnection = new BusiDatabaseManager();
		this.conn = databaseConnection.getConnection();
	}
	
	
	/**
	 * @version 1.0
	 * @see 
	 */
	public boolean insertGoods(GoodsTB goods){
		SqlMachine sqlMachine = new SqlMachine(conn);
		HashMap<String, Object> insertMap = new HashMap<String, Object>();

		insertMap.put(GoodsTB.NAME_GOODS, goods.getName_goods());
		insertMap.put(GoodsTB.UNIT_GOODS, goods.getUnit_goods());
		insertMap.put(GoodsTB.BARCODE_GOODS, goods.getBarcode_goods());
		insertMap.put(GoodsTB.URL_GOODS, goods.getUrl_goods());
		insertMap.put(GoodsTB.LEVEONE_GOODS, goods.getLeveone_goods());
		insertMap.put(GoodsTB.LEVETWO_GOODS, goods.getLevetwo_goods());
		insertMap.put(GoodsTB.REMARK_GOODS, goods.getRemark_goods());
		
		insertMap.put(GoodsTB.IMAGE_GOODS, goods.getImage_goods()); 
		insertMap.put(GoodsTB.KG_GOODS, goods.getKg_goods()); 
		//
		insertMap.put(GoodsTB.USEREMAIL_GOODS, goods.getUseremail_goods());
		insertMap.put(GoodsTB.USERNAME_GOODS, goods.getUsername_goods()); 
		
		insertMap.put(GoodsTB.CNSPELL_GOODS, CNSpell.CNToSpell(goods.getName_goods()));
		insertMap.put(GoodsTB.FWCNSPELL_GOODS, CNSpell.CNToFirstWordSpell(goods.getName_goods())); 
		
		
		
		
		return sqlMachine.insertRow(GoodsTB.TABLE_NAME, insertMap);
	}
	
	
	
	/**
	 * @version 1.0
	 * @see 
	 */
	public List<GoodsTB> selectListBySql(String sql){
		List<GoodsTB> goodsList = null;
		ResultSet resultSet = databaseConnection.executeQuery(conn, sql);
		
		if(resultSet != null){
				try {
					goodsList = new ArrayList<GoodsTB>();
					while(resultSet.next()){
						GoodsTB goods = new GoodsTB();
						
						goods.setName_goods(resultSet.getString(GoodsTB.NAME_GOODS));
						goods.setUnit_goods(resultSet.getString(GoodsTB.UNIT_GOODS));
						goods.setBarcode_goods(resultSet.getString(GoodsTB.BARCODE_GOODS));
						goods.setUrl_goods(resultSet.getString(GoodsTB.URL_GOODS));
						goods.setRemark_goods(resultSet.getString(GoodsTB.REMARK_GOODS));
						goods.setLeveone_goods(resultSet.getString(GoodsTB.LEVEONE_GOODS));
						goods.setLevetwo_goods(resultSet.getString(GoodsTB.LEVETWO_GOODS));
						goods.setImage_goods(resultSet.getString(GoodsTB.IMAGE_GOODS)); 
						
						goods.setKg_goods(resultSet.getDouble(GoodsTB.KG_GOODS)); 
						
						goods.setId_goods(resultSet.getInt(GoodsTB.ID_GOODS));
						goods.setNumber_goods(resultSet.getInt(GoodsTB.NUMBER_GOODS));
						goods.setPrice_goods(resultSet.getDouble(GoodsTB.PRICE_GOODS));
						goods.setAverage_goods(resultSet.getDouble(GoodsTB.AVERAGE_PURCHASE));
						//goods
						
				
						goodsList.add(goods);
					}
					resultSet.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}
		
		return goodsList;
	
	}
	
	public List<GoodsTB> selectGoods(int beginPageNum,int pageSize){
		
		List<GoodsTB> goodsList = null;
		String sql = "SELECT * FROM " + GoodsTB.TABLE_NAME  +" ORDER BY "+GoodsTB.ID_GOODS+" DESC limit "+beginPageNum+","+pageSize; 
		goodsList = selectListBySql(sql);
		
		return goodsList;
	}
	
	public List<GoodsTB> selectGoodsOrderByName(int beginPageNum,int pageSize){
		
		List<GoodsTB> goodsList = null;
		String sql = "SELECT * FROM " + GoodsTB.TABLE_NAME  +" ORDER BY "+GoodsTB.NAME_GOODS+" DESC limit "+beginPageNum+","+pageSize; 
		goodsList = selectListBySql(sql);
		
		return goodsList;
	}
	
	public List<GoodsTB> selectAll(){
		
		List<GoodsTB> goodsList = null;
		String sql = "SELECT * FROM " + GoodsTB.TABLE_NAME  +" ORDER BY "+GoodsTB.ID_GOODS+" DESC "; 
		goodsList = selectListBySql(sql);
		
		return goodsList;
	}
	
	public List<GoodsTB> select(String begindate,String enddate){
		
		List<GoodsTB> goodsList = null;
		String sql =  
		
		"SELECT * FROM goods_warehouse " +
		"JOIN  receipt_warehouse ON id_goods=goodsid_receipt " +
		"WHERE date_receipt " +
		" BETWEEN '"+begindate+"' AND '"+enddate+"' GROUP BY goodsid_receipt";
		goodsList = selectListBySql(sql);
		
		return goodsList;
	}
	/**
	 * @version 1.0
	 * @see 
	 */
	public int getRowNum(){
		
		int rowNum = 0;
		String sql = "select count(*) as rowNum from " + GoodsTB.TABLE_NAME ;
		
		ResultSet resultSet = databaseConnection.executeQuery(conn, sql);
		if(resultSet != null){
			try {
				
				while(resultSet.next()){
					rowNum = resultSet.getInt("rowNum");
				}
				resultSet.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return rowNum;
	}
	
	public GoodsTB getGoodsById(int id){
		
		String sql = "SELECT * FROM " + GoodsTB.TABLE_NAME + " where " + GoodsTB.ID_GOODS + "=" + id;
		GoodsTB goods = new GoodsTB();
		List<GoodsTB> goodsList = selectListBySql(sql);
		goods = goodsList.get(0);
		return goods;
	} 
	
	/**
	 * @version 1.0
	 * @author Shea
	 */
	public boolean upadteGoods(GoodsTB goods){
		SqlMachine sqlMachine = new SqlMachine(conn);
		HashMap<String, Object> updateMap = new HashMap<String, Object>();
		updateMap.put(GoodsTB.ID_GOODS, goods.getId_goods());
		updateMap.put(GoodsTB.NAME_GOODS, goods.getName_goods());
		updateMap.put(GoodsTB.UNIT_GOODS, goods.getUnit_goods());
		updateMap.put(GoodsTB.BARCODE_GOODS, goods.getBarcode_goods());
		updateMap.put(GoodsTB.URL_GOODS, goods.getUrl_goods());
		//updateMap.put(GoodsTB.IMAGE_GOODS, goods.getImage_goods());
		updateMap.put(GoodsTB.LEVEONE_GOODS, goods.getLeveone_goods()); 
		updateMap.put(GoodsTB.LEVETWO_GOODS, goods.getLevetwo_goods());
		updateMap.put(GoodsTB.REMARK_GOODS, goods.getRemark_goods());
		
		updateMap.put(GoodsTB.KG_GOODS, goods.getKg_goods());
		
		updateMap.put(GoodsTB.CNSPELL_GOODS, CNSpell.CNToSpell(goods.getName_goods()));
		updateMap.put(GoodsTB.FWCNSPELL_GOODS, CNSpell.CNToFirstWordSpell(goods.getName_goods())); 
		
		return sqlMachine.updateRow(GoodsTB.TABLE_NAME, updateMap, GoodsTB.ID_GOODS, goods.getId_goods());
	}
	
	/**
	 * @version 1.0
	 * @author Shea
	 */
	public boolean upadteGoodsImageByGoodsid(int goodsid,String image){
		SqlMachine sqlMachine = new SqlMachine(conn);
		HashMap<String, Object> updateMap = new HashMap<String, Object>();
		
		
	
		updateMap.put(GoodsTB.IMAGE_GOODS, image); 
		
		return sqlMachine.updateRow(GoodsTB.TABLE_NAME, updateMap, GoodsTB.ID_GOODS, goodsid);
	}
	
	/**
	 * @version 1.0
	 * @author Shea
	 */
	public boolean upadteGoodsAverageAndNumById(int id,int sumNum,double average,double newPrice){
		
		SqlMachine sqlMachine = new SqlMachine(conn);
		//sqlMachine.countField(GoodsTB., GoodsTB.TABLE_NAME);
		HashMap<String, Object> updateMap = new HashMap<String, Object>();
		
		updateMap.put(GoodsTB.NUMBER_GOODS, sumNum);
		updateMap.put(GoodsTB.AVERAGE_PURCHASE, average);
		updateMap.put(GoodsTB.PRICE_GOODS, newPrice);
		
		
		return sqlMachine.updateRow(GoodsTB.TABLE_NAME, updateMap, GoodsTB.ID_GOODS, id);
	}
	
	/**
	 * @version 1.0
	 * @author Shea
	 */
	public boolean upadteGoodsAverageAndNumById(int id,int sumNum,double average){
		
		SqlMachine sqlMachine = new SqlMachine(conn);
		//sqlMachine.countField(GoodsTB., GoodsTB.TABLE_NAME);
		HashMap<String, Object> updateMap = new HashMap<String, Object>();
		
		updateMap.put(GoodsTB.NUMBER_GOODS, sumNum);
		updateMap.put(GoodsTB.AVERAGE_PURCHASE, average);

		
		
		return sqlMachine.updateRow(GoodsTB.TABLE_NAME, updateMap, GoodsTB.ID_GOODS, id);
	}
	
	/**
	 * @version 1.0
	 * @author Shea
	 */
	public int getMaxIdByEmail(String email){
		//String sql = "select * from "+ReleaseTB.TABLE_NAME+" order by  "+ReleaseTB.RELEASE_DATE+" desc limit "+beginPageNum+","+pageSize; 
		SqlMachine sqlMachine = new SqlMachine(conn);
		int maxId = sqlMachine.getMaxId(GoodsTB.ID_GOODS, GoodsTB.TABLE_NAME, GoodsTB.USEREMAIL_GOODS, email);
		return maxId;
	}
	
	
	/**
	 * @version 1.0
	 * @author Shea
	 */
	public List<GoodsTB> selectBySearchStr(String searchStr,int beginPageNum,int pageSize){
		String sql = "select * from "+GoodsTB.TABLE_NAME+" WHERE  "
		+GoodsTB.NAME_GOODS+" LIKE '%"+searchStr+"%'"
		+" OR "+GoodsTB.ID_GOODS+" LIKE '%"+searchStr+"%'"
		+" OR "+GoodsTB.CNSPELL_GOODS+" LIKE '%"+searchStr+"%'"
		+" OR "+GoodsTB.FWCNSPELL_GOODS+" LIKE '%"+searchStr+"%'"
		+" limit "+beginPageNum+","+pageSize; 
		//ResultSet resultSet = databaseConnection.executeQuery(conn, sql);
		return selectListBySql(sql);
	}
	
	/**
	 * @version 1.0
	 * @author Shea
	 */
	public List<GoodsTB> selectByGoodsclassidStr(String goodsclassidStr,int beginPageNum,int pageSize){
		if(goodsclassidStr.length() < 3){
			return null;
		}
		
		String sql = "SELECT * FROM goods_warehouse,goodsclasslist_warehouse WHERE "
		+" goodsid_goodsclasslist=id_goods AND goodsclassid_goodsclasslist IN " + goodsclassidStr
		+ " GROUP BY id_goods "
		+" limit "+beginPageNum+","+pageSize; 
		//ResultSet resultSet = databaseConnection.executeQuery(conn, sql);
		return selectListBySql(sql);
	}
	
	/**
	 * @version 1.0
	 * @see 
	 */
	public int getRowNumForGoodsclassidStr(String goodsclassidStr){
		
		int rowNum = 0;
		
		String sql = "SELECT count(*) as rowNum FROM goods_warehouse,goodsclasslist_warehouse WHERE "
			+"goodsid_goodsclasslist=id_goods AND goodsclassid_goodsclasslist IN " + goodsclassidStr;
		
		ResultSet resultSet = databaseConnection.executeQuery(conn, sql);
		if(resultSet != null){
			try {
				
				while(resultSet.next()){
					rowNum = resultSet.getInt("rowNum");
				}
				resultSet.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return rowNum;
	}
	
	
	/**
	 * @version 1.0
	 * @see 
	 */
	public int getRowNumForSearchStr(String searchStr){
		
		int rowNum = 0;
		String sql = "select count(*) as rowNum from " + GoodsTB.TABLE_NAME +" WHERE  "
		+GoodsTB.NAME_GOODS+" LIKE '%"+searchStr+"%'"
		+" OR "+GoodsTB.ID_GOODS+" LIKE '%"+searchStr+"%'"
		+" OR "+GoodsTB.CNSPELL_GOODS+" LIKE '%"+searchStr+"%'"
		+" OR "+GoodsTB.FWCNSPELL_GOODS+" LIKE '%"+searchStr+"%'"; 
		
		ResultSet resultSet = databaseConnection.executeQuery(conn, sql);
		if(resultSet != null){
			try {
				
				while(resultSet.next()){
					rowNum = resultSet.getInt("rowNum");
				}
				resultSet.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return rowNum;
	}
	
	
	/**
	 * @version 1.0
	 * @author Shea
	 */
	public double getCost(){
		
		double num = 0;
		String sql = "select SUM("+GoodsTB.NUMBER_GOODS+"*"+GoodsTB.AVERAGE_PURCHASE+") as num from " + GoodsTB.TABLE_NAME;
		
		ResultSet resultSet = databaseConnection.executeQuery(conn, sql);
		if(resultSet != null){
			try {
				
				while(resultSet.next()){
					num = resultSet.getDouble("num");
				}
				resultSet.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return num;
	}
	
	
	/**
	* 导出Excel表
	*  
	* @param rs
	* 数据库结果集
	* @param filePath
	* 要保存的路径，文件名为 fileName.xls
	* @param sheetName
	* 工作簿名称 工作簿名称，本方法目前只支持导出一个Excel工作簿
	* @param columnName
	* 列名，类型为Vector<STRING></STRING>
	*/
	public static void write2Excel(String fileName,
		HttpServletResponse response,List<GoodsTB> goodsList,Map<Integer,List<PurchaseTB>> warehouseInfoMapList,int number) throws SQLException, IOException,
		RowsExceededException, WriteException {
		// 获得导出数据的列数
		String[] title = { "产品ID", "名称", "数量"};
		
		response.setContentType("application/x-msdownload");
		// 定义打印出来后的报表名
		String dispposition = "=?UTF-8?Q?attachment; filename=" + fileName
		+ ".xls";
		response.setHeader("Content-Disposition", dispposition);
		
		
		
		OutputStream os = response.getOutputStream();
		
		WritableWorkbook wwb = Workbook.createWorkbook(os);
		WritableSheet ws = wwb.createSheet(fileName, 0);
		
		try{
				
			for(int i=0; i<title.length;i++){
				Label label1 = new Label(i, 0, title[i]);
				ws.addCell(label1);
			}
			
			int i=0;
			for(GoodsTB goods :goodsList){
				if(goods.getNumber_goods() >= number){
					i++;
					Label label1 = new Label(0, i, goods.getId_goods()+"");
					Label label2 = new Label(1, i, goods.getName_goods());
					
					List<PurchaseTB> purchaseList = warehouseInfoMapList.get(goods.getId_goods());
					String nubmerStr = "";
					for(PurchaseTB purchase:purchaseList){
						nubmerStr += purchase.getWarehousename_purchase() + ":" + purchase.getReserve_purchase() + "     \r\n";
					}
					
					
					
					Label label3 = new Label(2, i, nubmerStr+"");
			
					ws.addCell(label1);
					ws.addCell(label2);
					ws.addCell(label3);
				}
				
		
					
			}
			
			wwb.write();
		
		} catch (Exception ex) {
		ex.printStackTrace();
		} finally {
		// 关闭Excel工作薄对象
		
	
		wwb.close();
		
		os.close();
		}
	}
	
	public Map<Integer,GoodsTB> getMapBySalesReports(String inStr){
		if(inStr.length() < 3){
			return null;
		}
		
		String sql = "SELECT * FROM " + GoodsTB.TABLE_NAME  
			+ " where " + GoodsTB.ID_GOODS + " IN "+inStr+""; 
		
		List<GoodsTB> list = new ArrayList<GoodsTB>();
		list = selectListBySql(sql);
	
		
		
		Map<Integer,GoodsTB> valueMap = new HashMap<Integer,GoodsTB>();
		
		for(GoodsTB obj:list){
			
			valueMap.put(obj.getId_goods(),obj);//修改正最后一次加入
		}
		
		
		
		return valueMap;
	}
	
	public Map<Integer,GoodsTB> getMapByInstr(String inStr){
		if(inStr.length() < 3){
			return null;
		}
		
		String sql = "SELECT * FROM " + GoodsTB.TABLE_NAME  
			+ " where " + GoodsTB.ID_GOODS + " IN "+inStr+""; 
		
		List<GoodsTB> list = new ArrayList<GoodsTB>();
		list = selectListBySql(sql);
	
		
		
		Map<Integer,GoodsTB> valueMap = new HashMap<Integer,GoodsTB>();
		
		for(GoodsTB obj:list){
			
			valueMap.put(obj.getId_goods(),obj);//修改正最后一次加入
		}
		
		
		
		return valueMap;
	}
	
}
